/**
 * @(#) POIUtils.java
 *
 * @author lihg
 * @version V1.0 2013-10-16
 *
 */

package cn.com.dshome.common.util;

import java.awt.Font;
import java.io.OutputStream;
import java.util.Date;

/**
 * 
 * Purpose:
 * 
 * @author lihg
 * @see
 * @since
 */

public class POIUtils {

	private static final short COLOR_PINK = 20;

	private static final short COLOR_BROWN = 21;

	private static final short COLOR_YELLOW = 22;

	private static final short COLOR_DARK_BLUE = 23;

	private static final short COLOR_GREEN = 24;

	private static final short COLOR_EARTH = 25;

	private static final short COLOR_DARK_RED = 26;

	private static final short COLOR_PURPLE = 27;

	private HSSFWorkbook workBook;
	private OutputStream outPutStream = null;
	private HSSFFont timesNewRomanNinePixNoneColor;
	private HSSFFont songNinePix;
	private HSSFSheet sheet;
	private HSSFCellStyle dateStyle;

	public POIUtils(OutputStream outPutStream,String sheetName) {
		workBook = new HSSFWorkbook();
		this.outPutStream = outPutStream ;
		short fontBold = Font.BOLDWEIGHT_BOLD;
		timesNewRomanNinePixNoneColor = getFont("Times New Roman", 9, fontBold);
		songNinePix = getFont("宋体", 9, fontBold);
		dateStyle = setCellDataFormat("yyyy/MM/dd HH:mm:ss");
		if(null == sheetName || "".equalsIgnoreCase(sheetName)) {
			sheetName = "Sheet1" ;
		}
		sheet = workBook.createSheet(sheetName);
	}

	/**
	 * 设置字体样式
	 */
	private HSSFFont getFont(String fontName, int fontSize, short fontBold) {
		HSSFFont font = workBook.createFont();
		font.setBoldweight(fontBold);
		font.setFontName(fontName);
		font.setFontHeightInPoints((short) fontSize);
		return font;
	}

	/**
	 * 设置单元格内数据显示格式
	 * 
	 * @param workBook
	 * @param pattern
	 *            要显示的样式
	 * @return
	 */
	public HSSFCellStyle setCellDataFormat(String pattern) {
		HSSFDataFormat format = workBook.createDataFormat();
		HSSFCellStyle cellStyle = getDefaultCellStyle(timesNewRomanNinePixNoneColor);
		cellStyle.setDataFormat(format.getFormat(pattern));
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		return cellStyle;
	}

	/**
	 * 返回默認单元格样式 [水平、垂直居中，有边框]
	 * 
	 * @param workBook
	 * @param font
	 * @return
	 */
	private HSSFCellStyle getDefaultCellStyle(HSSFFont font) {
		HSSFCellStyle cellStyle = workBook.createCellStyle();
		short lineStyle = CellStyle.BORDER_THIN;
		cellStyle.setBorderTop(lineStyle);
		cellStyle.setBorderBottom(lineStyle);
		cellStyle.setBorderLeft(lineStyle);
		cellStyle.setBorderRight(lineStyle);
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cellStyle.setWrapText(true);
		cellStyle.setFont(font);
		return cellStyle;
	}

	/**
	 * 创建表头信息
	 * 
	 * @param sheet
	 * @param style
	 */
	public void createHead(String[] heads) {
		HSSFRow row = getRow(sheet, 0);
		HSSFCell cell;

		// 设置样式
		HSSFCellStyle style = getDefaultCellStyle(songNinePix);
		// 设置像素
		row.setHeight(setPix(8));
		for (int i = 0, j = 0; i < heads.length; i++) {
			String value = heads[i];
			cell = row.createCell(j);
			// 设置列宽
			sheet.setColumnWidth(j, setPix(150));
			// 设置前景色
			style.setFillPattern(CellStyle.SOLID_FOREGROUND);
			// 设置背景色
			style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
			cell.setCellStyle(style);
			cell.setCellValue(value);
			j++;
		}
	}

	/**
	 * 创建表内容
	 * 
	 * @param sheet
	 * @param list
	 * @param workBook
	 */
	public void createBody(Object[] obj) {
		HSSFCellStyle cellStyle = null;
		HSSFCell cell = null;
		HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
		row.setHeight(setPix(12));
		int index = 0;
		for (Object o : obj) {
			if (o != null) {
				cell = row.createCell(index);
				if (o instanceof Date) {
					cell.setCellValue((Date) o);
					cell.setCellStyle(dateStyle);
				} else if(o instanceof Double){
					cell.setCellValue((Double) o) ;
					cellStyle = getDefaultCellStyle(timesNewRomanNinePixNoneColor);
					cell.setCellStyle(cellStyle);
				}else {
					cellStyle = getDefaultCellStyle(timesNewRomanNinePixNoneColor);
					cell.setCellValue(obj == null ? "" : String.valueOf(o));
					cell.setCellStyle(cellStyle);
				}
			}
			index++;
		}
	}

	public void close() {
		try {
			workBook.write(outPutStream);
		} catch (Exception e) {
			e.printStackTrace() ;
		}
		try {
			if (outPutStream != null) {
				outPutStream.flush();
				outPutStream.close();
			}
		} catch (Exception e) {
			e.printStackTrace() ;
		}
	}
	/**
	 * 返回HSSFRow
	 * 
	 * @param sheet
	 * @param rowIndex
	 * @return
	 */
	private HSSFRow getRow(HSSFSheet sheet, int rowIndex) {
		HSSFRow row = sheet.getRow(rowIndex);
		if (row == null) {
			row = sheet.createRow(rowIndex);
		}
		return row;
	}

	/**
	 * 设置像素
	 * 
	 * @param pix
	 * @return
	 */
	public short setPix(int pix) {
		return (short) ((short) 37 * pix);
	}

	/**
	 * 创建下拉列表
	 * 
	 * @param sheet  要创建下拉列表的工作表
	 * @param listMsg列表信息
	 * @param rowFrom
	 * @param rowTo
	 * @param colFrom
	 * @param colTo
	 */
	public void createListBox(HSSFSheet sheet, String[] listMsg, int rowFrom,int rowTo, int colFrom, int colTo) {
		CellRangeAddressList regions = new CellRangeAddressList(rowFrom, rowTo,colFrom, colTo);
		DVConstraint constraint = DVConstraint.createExplicitListConstraint(listMsg);
		HSSFDataValidation validation = new HSSFDataValidation(regions,constraint);
		sheet.addValidationData(validation);
	}

	/**
	 * 设置自定义背景色
	 * 
	 * @param workBook
	 */
	public void setCustomBackGroundColor(HSSFWorkbook workBook) {
		HSSFPalette palette = workBook.getCustomPalette();
		palette.setColorAtIndex(POIUtils.COLOR_PINK, (byte) 255, (byte) 153, (byte) 204);
		palette.setColorAtIndex(POIUtils.COLOR_YELLOW, (byte) 255, (byte) 255,(byte) 153);
		palette.setColorAtIndex(POIUtils.COLOR_GREEN, (byte) 153, (byte) 204, (byte) 0);
		palette.setColorAtIndex(POIUtils.COLOR_DARK_BLUE, (byte) 0, (byte) 204,(byte) 255);
		palette.setColorAtIndex(POIUtils.COLOR_BROWN, (byte) 255, (byte) 153, (byte) 0);
		palette.setColorAtIndex(POIUtils.COLOR_EARTH, (byte) 255, (byte) 204,(byte) 153);
		palette.setColorAtIndex(POIUtils.COLOR_DARK_RED, (byte) 255, (byte) 128,(byte) 128);
		palette.setColorAtIndex(POIUtils.COLOR_PURPLE, (byte) 255, (byte) 0, (byte) 255);
	}
}


/**
 * Revision history
 * -------------------------------------------------------------------------
 * 
 * Date Author Note
 * -------------------------------------------------------------------------
 * 2013-10-16上午9:27:31 lihg 创建版本
 */